package com.example.dao.impl;

import com.example.dao.AdminDao;
import com.example.pojo.Admin;
import com.example.pojo.Announce;
import com.example.pojo.Help;
import com.example.pojo.User;
import com.example.utils.DBConnection;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository("adminDao")
public class AdminDaoImpl implements AdminDao {

    public Admin login(String username, String password) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Admin admin = null;
        try {
            conn = DBConnection.getConnection();
            String sql = "select a_id aId,username,password from admin where username = ?";

            ps = conn.prepareStatement(sql);
            ps.setString(1,username);
            rs = ps.executeQuery();

            while(rs.next()) {
                Integer aId = rs.getInt("aId");
                String passWord = rs.getString("password");
                if(!passWord.equals(password)) {
                    return admin;
                }
                admin = new Admin(aId,username,password);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        DBConnection.close(rs,ps,conn);
        return admin;
    }

    public Map<String,Object> getTopicData() {
        Connection conn = null;
        CallableStatement cs = null;
        ResultSet rs = null;
        Map<String,Object> topicDataMap = new HashMap<>();
        try {
            conn = DBConnection.getConnection();
            String sql = "call topicDataProcedure(?,?,?,?)";
            cs = conn.prepareCall(sql);
            cs.registerOutParameter(1, Types.INTEGER);
            cs.registerOutParameter(2, Types.INTEGER);
            cs.registerOutParameter(3,Types.INTEGER);
            cs.registerOutParameter(4,Types.INTEGER);
            cs.execute();
            topicDataMap.put("topicCount",cs.getInt("topicCount"));
            topicDataMap.put("commentCount",cs.getInt("commentCount"));
            topicDataMap.put("bestTopic",cs.getInt("bestTopic"));
            topicDataMap.put("bestComment",cs.getInt("bestComment"));

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(cs != null) {
                try {
                    cs.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            DBConnection.close(null,null,conn);
            return topicDataMap;
        }
    }

    public Map<String,Object> getTypeCount() {
        Connection conn = null;
        CallableStatement cs = null;
        ResultSet rs = null;
        Map<String,Object> typeCountMap = new HashMap<>();
        try {
            conn = DBConnection.getConnection();
            String sql = "call typeCountProcedure(?,?,?,?,?)";
            cs = conn.prepareCall(sql);
            cs.registerOutParameter(1, Types.INTEGER);
            cs.registerOutParameter(2, Types.INTEGER);
            cs.registerOutParameter(3,Types.INTEGER);
            cs.registerOutParameter(4,Types.INTEGER);
            cs.registerOutParameter(5,Types.INTEGER);
            cs.execute();
            typeCountMap.put("dayCount",cs.getInt("dayCount"));
            typeCountMap.put("knowCount",cs.getInt("knowCount"));
            typeCountMap.put("problemCount",cs.getInt("problemCount"));
            typeCountMap.put("helpCount",cs.getInt("helpCount"));
            typeCountMap.put("friendCount",cs.getInt("friendCount"));

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(cs != null) {
                try {
                    cs.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            DBConnection.close(null,null,conn);
            return typeCountMap;
        }
    }

    public List<Map<String,Object>> getUserList() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Map<String,Object>> userList = new ArrayList<>();
        try {
            conn = DBConnection.getConnection();
            String sql = "select u_id uId,username,password,nickname,name,sex,avatar,phone,id_number idNumber," +
                    "enter_year enterYear," +
                    "class_name className,subject,email,topic_count topicCount,comment_count commentCount from user";

            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()) {
                Map<String,Object> map = new HashMap<>();
                map.put("uId",rs.getInt("uId"));
                map.put("username",rs.getString("username"));
                map.put("password",rs.getString("password"));
                map.put("nickname",rs.getString("nickname"));
                map.put("name",rs.getString("name"));
                map.put("sex",rs.getString("sex"));
                map.put("avatar",rs.getString("avatar"));
                map.put("phone",rs.getString("phone"));
                map.put("idNumber",rs.getString("idNumber"));
                map.put("enterYear",rs.getString("enterYear"));
                map.put("className",rs.getString("className"));
                map.put("subject",rs.getString("subject"));
                map.put("email",rs.getString("email"));
                map.put("topicCount",rs.getString("topicCount"));
                map.put("commentCount",rs.getString("commentCount"));
                userList.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        DBConnection.close(rs,ps,conn);
        return userList;
    }

    public Integer addUser(String username,String password,String nickname,String name,String sex,String avatar,
                           String phone,String idNumber,String enterYear,String className,String subject,String email) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int count = -1;
        try{
            conn = DBConnection.getConnection();
            String sql = "insert into user(username,password,nickname,name,sex,avatar,phone,id_number,enter_year," +
                    "class_name,subject,email) values(?,?,?,?,?,?,?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1,username);
            ps.setString(2,password);
            ps.setString(3,nickname);
            ps.setString(4,name);
            ps.setString(5,sex);
            ps.setString(6,avatar);
            ps.setString(7,phone);
            ps.setString(8,idNumber);
            ps.setString(9,enterYear);
            ps.setString(10,className);
            ps.setString(11,subject);
            ps.setString(12,email);
            count = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
        DBConnection.close(rs,ps,conn);
        return count;
    }

    public List<Map<String,Object>> getTopicList() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Map<String,Object>> topicList = new ArrayList<>();
        try {
            conn = DBConnection.getConnection();
            String sql = "select topic.t_id tId,type,title,nickname,publish_time publishTime,likes,collection_times " +
                    "collectionTimes,real_status realStatus,check_status checkStatus from topic,user where topic" +
                    ".u_id = " +
                    "user.u_id order by publish_time desc";

            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()) {
                Map<String,Object> map = new HashMap<>();
                map.put("tId",rs.getInt("tId"));
                map.put("type",rs.getInt("type"));
                map.put("title",rs.getString("title"));
                map.put("nickname",rs.getString("nickname"));
                map.put("publishTime",rs.getString("publishTime"));
                map.put("likes",rs.getString("likes"));
                map.put("collectionTimes",rs.getString("collectionTimes"));
                map.put("realStatus",rs.getString("realStatus"));
                map.put("checkStatus",rs.getString("checkStatus"));
                topicList.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        DBConnection.close(rs,ps,conn);
        return topicList;
    }

    public List<Announce> getAnnounceList() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Announce> announceList = new ArrayList<>();
        try {
            conn = DBConnection.getConnection();
            String sql = "select an_id anId,a_id aId,avatar,title,content,likes,read_times readTimes,publish_time " +
                    "publishTime" +
                    " from " +
                    "announce order by publish_time desc";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()) {
                Integer anId = rs.getInt("anId");
                Integer aId = rs.getInt("aId");
                String avatar = rs.getString("avatar");
                String title = rs.getString("title");
                String content = rs.getString("content");
                Integer likes = rs.getInt("likes");
                Integer readTimes = rs.getInt("readTimes");
                String publishTime = rs.getString("publishTime").substring(0,16);
                Announce announce = new Announce(anId,aId,avatar,title,content,likes,readTimes,publishTime);
                announceList.add(announce);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        DBConnection.close(rs,ps,conn);
        return announceList;
    }

    public List<Help> getHelpList() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Help> helpList = new ArrayList<>();
        try {
            conn = DBConnection.getConnection();
            String sql = "select h_id hId,a_id aId,avatar,title,content,likes,read_times readTimes,publish_time " +
                    "publishTime" +
                    " from " +
                    "help order by publish_time desc";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()) {
                Integer hId = rs.getInt("hId");
                Integer aId = rs.getInt("aId");
                String avatar = rs.getString("avatar");
                String title = rs.getString("title");
                String content = rs.getString("content");
                Integer likes = rs.getInt("likes");
                Integer readTimes = rs.getInt("readTimes");
                String publishTime = rs.getString("publishTime").substring(0,16);
                Help help = new Help(hId,aId,avatar,title,content,likes,readTimes,publishTime);
                helpList.add(help);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        DBConnection.close(rs,ps,conn);
        return helpList;
    }

    public Integer insertAnnounce(Integer aId,String avatar,String title,String content) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int count = -1;
        try {
            conn = DBConnection.getConnection();
            String sql = "insert into announce(a_id,avatar,title,content) values(?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,aId);
            ps.setString(2,avatar);
            ps.setString(3,title);
            ps.setString(4,content);
            count = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
        DBConnection.close(rs,ps,conn);
        return count;
    }

    public Integer insertHelp(Integer aId,String avatar,String title,String content) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int count = -1;
        try {
            conn = DBConnection.getConnection();
            String sql = "insert into help(a_id,avatar,title,content) values(?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,aId);
            ps.setString(2,avatar);
            ps.setString(3,title);
            ps.setString(4,content);
            count = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
        DBConnection.close(rs,ps,conn);
        return count;
    }

    public Integer deleteTopic(Integer tId) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int count = -1;
        try {
            conn = DBConnection.getConnection();
            String sql = "delete from topic where t_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,tId);
            count = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
        DBConnection.close(rs,ps,conn);
        return count;
    }

    public Integer checkTopic(Integer tId,String checkStatus) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int count = -1;
        try {
            conn = DBConnection.getConnection();
            String sql = "update topic set check_status = ? where t_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,checkStatus);
            ps.setInt(2,tId);
            count = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
        DBConnection.close(rs,ps,conn);
        return count;
    }
}
